/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Control;

import Boundary.*;
import Boundary.siswa;
import Form.EditGuru;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Fave
 */
public class GuruKontrol {
    private Connection conn;

    public GuruKontrol(Connection koneksi)
    {
        this.conn = koneksi;
    }

    public static GuruKontrol getKoneksi() throws SQLException {
        GuruKontrol kon = new GuruKontrol(Tools.DataBaseConnection.KoneksiDataBase());
        return kon;
    }

    public void UpdatePresensi(Presensi p) throws SQLException {
        String tgl = p.getTanggal();
        String nis = p.getNis().getUsername();
        int kode = p.getKodekehadiran();
        String sql = "update rpltpresensi"
                + " set kodekehadiran = "+kode
                + " where nis = '"+nis+"'"
                + " and tanggal = '"+tgl+"'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
    }

    public List<Guru> getAll(String tanggal, String kelas) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String tgl = tanggal;
        String kls = kelas;
        String sql = "select nis, nama, kodekehadiran " +
                    "from rpltpresensi join rpltsiswa " +
                    "using(nis) where upper(tanggal) like '"+tgl+"' and kelas = '"+kls+"' order by nis";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<Guru> gr = new ArrayList<Guru>();
        while (rset.next()) {
            Guru g = new Guru();
            siswa s = new siswa();
            Presensi p = new Presensi();
            s.setNIS(rset.getString(1));
            g.setNis(s);
            s.setNama(rset.getString(2));
            g.setNama(s);
            p.setKodekehadiran(rset.getInt(3));
            g.setKodekehadiran(p);
            gr.add(g);
        }
        conn.commit();
        return gr;
    }

      public String cariNIS(String nis) throws SQLException {
        CallableStatement callst = null;
        String namanya="";

        try {
            String sql = "{CALL spcarins(?,?)}";
            callst = conn.prepareCall(sql);
            callst.setString(1, nis);
            callst.registerOutParameter(2, java.sql.Types.VARCHAR);
            callst.execute();
            namanya = callst.getString(2);
            callst.execute();
//            System.out.println("nama");
            conn.commit();
            return namanya;
        } catch (SQLException e) {
            Logger.getLogger(EditGuru.class.getName()).log(Level.SEVERE, null, e);
            conn.rollback();
            throw e;
        }
    }
}
